<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> 
        <meta name="language" content="sk" /> 
        
<style type="text/css">
#report
{
font-family:font-family: Verdana, Arial, Helvetica, sans-serif;
width:100%;
border-collapse:collapse;
}
td, th 
{
font-size:1em;
border:1px solid #98bf21;
padding:3px 7px 2px 7px;
}
th 
{
font-size:16px;
text-align:left;
padding-top:5px;
padding-bottom:4px;
background-color:#A7C942;
color:#ffffff;
}
tr.alt td 
{
color:#000000;
background-color:#EAF2D3;
}
</style>
</head>
<body>
<h2>Report #4: Status študentov a knižničných účtov</h2>
<table id="report">

<tr>
<th><b>AIS ID</b></th>
<th><b>Meno</b></th>
<th><b>Priezvisko</b></th>
<th><b>Študijný program</b></th>
<th><b>Ročník</b></th>
<th><b>Študijný status</b></th>
<th><b>ID v knižnici</b></th>
<th><b>Knižničný status</b></th>
<th><b>Počet nevrátených publikácií</b></th>
</tr>

<?php 

	function getConnection() {
		$con = mysql_pconnect('147.175.187.53', 'root', 'qwerty123', 65536);
		if (!$con) {
			echo('<h3 style="color: Red;">Cannot connect to MySQL server &nbsp;&nbsp; :\'(</h3>');
			die('Could not connect: ' . mysql_error());
		}
		mysql_query("SET NAMES 'utf8'");
		mysql_select_db("dwh", $con);
		return $con;
	}
	
	$connection = getConnection();	
	$queryString = "SELECT s.AIS_ID, s.FirstName, s.lastName,fs.Name,s.Grade,ss.Name AS 'studyStatus',la.LibraryAccountKey,la.Active  FROM `dwh`.`libraryaccount` la
					left join `dwh`.`student` s on s.StudentKey=la.StudentKey
					left join `dwh`.`fieldofstudy` fs on fs.FieldOfStudyKey=s.FieldOfStudyKey
					left join `dwh`.`studystatus` ss on ss.StudyStatusKey=s.StudyStatusKey
					WHERE (ss.Name='štúdium ukoncené vylúcením' OR ss.Name='prerušené štúdium' OR ss.Name='úspešne absolvované štúdium') AND la.Active=1;" ;
	$queryAnswer = mysql_query($queryString,$connection)or die(mysql_error()); 
	
	while($row = mysql_fetch_array($queryAnswer) ) {
		
				echo "<tr>";
				echo "<td>".$row["AIS_ID"]."</td>";
				echo "<td>".$row["FirstName"]."</td>";
				echo "<td>".$row["lastName"]."</td>";
				echo "<td>".$row["Name"]."</td>";
				echo "<td>".$row["Grade"]."</td>";
				echo "<td>".$row["studyStatus"]."</td>";
				echo "<td>".$row["LibraryAccountKey"]."</td>";
				echo "<td>".$row["Active"]."</td>";
				$queryString2 = "SELECT COUNT(LibraryAccountKey) FROM `dwh`.`borrowingfacts` WHERE LibraryAccountKey=".$row["LibraryAccountKey"].";" ;
					$queryAnswer2 = mysql_query($queryString2,$connection)or die(mysql_error()); 
					
					while($row = mysql_fetch_array($queryAnswer2) ) {
						echo "<td>".$row["COUNT(LibraryAccountKey)"]."</td>";
					}
	
				echo "</tr>";
	}
	
	mysql_close($connection);
?>
</table>

</body>
</html>